/*******************************************************************************
 * History of Modifications:
 *******************************************************************************
 * Date:        Author:         Description:
 * 02-May-2008  Amir Nasiruddin Sayani   Created
 *
 *
 *******************************************************************************
 */

using System.Data;
using System.Data.SqlClient;
//using System.Data.Sql;
using System.Configuration;
namespace FYP14
{
	public class Database
	{
		private static System.Collections.Hashtable SQLparamCache = System.Collections.Hashtable.Synchronized(new System.Collections.Hashtable());
        private SqlConnection Connection = new SqlConnection();
        public static string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
		private SqlCommand DbCommand=new SqlCommand(); 
		private SqlDataAdapter DtAdapter=new SqlDataAdapter();
		private DataSet SQLDataSet=new DataSet(); 
		private DataTable SQLTable =new System.Data.DataTable();
		
		public void UnLoadSpParameters()
		{
			DbCommand.Parameters.Clear();
		}
		public void LoadSpParameters(string SpName , params object[] ParaValues)
		{
            if (ParaValues == null)
            {
                ParaValues = new object[] { null };

            }

            SqlParameter[] TheParameters = (SqlParameter[])SQLparamCache[SpName]; 
			DbCommand.Parameters.Clear();
			if (TheParameters==null)
			{
                DbCommand.CommandType = CommandType.StoredProcedure;
				DbCommand.CommandText = SpName;
				SqlCommandBuilder.DeriveParameters(DbCommand);
                TheParameters = new SqlParameter[DbCommand.Parameters.Count];				
				DbCommand.Parameters.CopyTo(TheParameters,0);
                SQLparamCache[SpName] = TheParameters;					
			}
			else
			{
				short i;
                SqlParameter SqPr;
				DbCommand.CommandType = CommandType.StoredProcedure;
				DbCommand.CommandText = SpName;
				for(i=0;i<TheParameters.Length;i++)
				{
                    SqPr = (SqlParameter)(((System.ICloneable)(TheParameters[i])).Clone());
					DbCommand.Parameters.Add(SqPr);
				
				}
			}
			MoveParameters(ParaValues);
		}
        public void LoadSpParameters(string SpName, SqlTransaction Trans,SqlConnection Conn, params object[] ParaValues)
        {
            SqlParameter[] TheParameters = (SqlParameter[])SQLparamCache[SpName];
            DbCommand.Parameters.Clear();
            if (TheParameters == null)
            {
                DbCommand.Connection = Conn;
                DbCommand.Transaction =Trans; 
                DbCommand.CommandType = CommandType.StoredProcedure;
                DbCommand.CommandText = SpName;
                SqlCommandBuilder.DeriveParameters(DbCommand);
                TheParameters = new SqlParameter[DbCommand.Parameters.Count];
                DbCommand.Parameters.CopyTo(TheParameters, 0);
                SQLparamCache[SpName] = TheParameters;
            }
            else
            {
                short i;
                SqlParameter SqPr;
                DbCommand.CommandType = CommandType.StoredProcedure;
                DbCommand.CommandText = SpName;
                for (i = 0; i < TheParameters.Length; i++)
                {
                    SqPr = (SqlParameter)(((System.ICloneable)(TheParameters[i])).Clone());
                    DbCommand.Parameters.Add(SqPr);

                }
            }
            MoveParameters(ParaValues);
        }
		private void MoveParameters(object[] Paras)
		{
			short ic;
            SqlParameter Para;
			if (Paras.Length >= 0)
			{
				for (ic=0;ic<Paras.Length;ic++)
				{
					Para = DbCommand.Parameters[ic];
					//string  s = Para.ParameterName;
					Para.Value= Paras[ic];
				}
			}
		}
		/*public OracleParameter Parameters(int P)
		{
			return DbCommand.Parameters[P];
		}*/
		public bool OpenConnection()
		{
			try
			{
				if (Connection.State == ConnectionState.Open) return true;
                Connection = new SqlConnection();
				Connection.ConnectionString = ConnectionString;
				Connection.Open();
				if (Connection.State == ConnectionState.Open)
				{
					DbCommand.Connection = Connection;
					return true;
				}
				else
				{
					return false;
				}

			}
			catch (System.Exception ee)
			{
				throw new System.Exception("Database:OpenConnection:" + ee.Message);
			}
		}
		public void CloseConnection()
		{
			if (Connection.State != ConnectionState.Closed) Connection.Close();
			DbCommand.Dispose();
			DbCommand = null;
			DtAdapter.Dispose();
			DtAdapter = null;
			SQLDataSet.Dispose();
			SQLDataSet = null;
			SQLTable.Dispose();
			SQLTable = null;
		}
        public SqlDataReader GetDataReader()
        {
            return DbCommand.ExecuteReader();
        }
        public SqlDataReader GetDataReader(string Statement)
        {
                    DbCommand.CommandType = CommandType.Text;
                    DbCommand.CommandText = Statement;
                    return DbCommand.ExecuteReader();		
        }
		public int ExecuteQuery()
		{
			return DbCommand.ExecuteNonQuery();
		}
        public int ExecuteQuery(SqlTransaction Trans,SqlConnection Conn)
        {
            DbCommand.Connection = Conn; 
            DbCommand.Transaction = Trans;    
            return DbCommand.ExecuteNonQuery();
        }
		public int ExecuteQuery(string Statement)
		{
			DbCommand.CommandType = CommandType.Text;
			DbCommand.CommandText = Statement;
			return DbCommand.ExecuteNonQuery();
        }
        public int ExecuteQuery(string Statement, SqlTransaction Trans)
        {
            DbCommand.Transaction = Trans;    
            DbCommand.CommandType = CommandType.Text;
            DbCommand.CommandText = Statement;
            return DbCommand.ExecuteNonQuery();
        }
        public int ExecuteQuery(string Statement, SqlTransaction Trans,SqlConnection Conn)
        {
            DbCommand.Connection = Conn;  
            DbCommand.Transaction = Trans;
            DbCommand.CommandType = CommandType.Text;
            DbCommand.CommandText = Statement;
            return DbCommand.ExecuteNonQuery();
        }
        
        public object ExecuteValue()
		 {
			return DbCommand.ExecuteScalar();
		 }
		 public object ExecuteValue(string Statement)
		 {
			DbCommand.CommandType = CommandType.Text;
			DbCommand.CommandText = Statement;
			return DbCommand.ExecuteScalar();
		 }

		public DataSet GetDateSet()
		{
			DtAdapter.SelectCommand = DbCommand;
			DtAdapter.Fill(SQLDataSet);
			return SQLDataSet;
		}
		public DataSet GetDateSet(string Statement)
		{
			DbCommand.CommandType = CommandType.Text;
			DbCommand.CommandText = Statement;
			DtAdapter.SelectCommand = DbCommand;
			DtAdapter.Fill(SQLDataSet);
			return SQLDataSet;
		}
		public DataTable GetDataTable()
		{
			DtAdapter.SelectCommand = DbCommand;
            DtAdapter.Fill(SQLTable);
			return SQLTable;
		}
		public DataTable GetDataTable(string Statement)
		{
			DbCommand.CommandType = CommandType.Text;
			DbCommand.CommandText = Statement;
			DtAdapter.SelectCommand = DbCommand;
			DtAdapter.Fill(SQLTable);
			return SQLTable;
		}
		public void SetDataTable(DataTable DT)
		{
			DtAdapter.SelectCommand = DbCommand;
			DtAdapter.Fill(DT);
		}
        public SqlDataAdapter GetDataAdapter(DataTable DT)
        {
            DtAdapter.SelectCommand = DbCommand;
            DtAdapter.Fill(DT);
            return DtAdapter;
        }
		public void SetDataTable(DataTable DT,string Statement)
		{
			DbCommand.CommandType = CommandType.Text;
			DbCommand.CommandText = Statement;
			DtAdapter.SelectCommand = DbCommand;
			DtAdapter.Fill(DT);
		}
        //public Database()
        //{
        //    string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        //}
	}
}
